Libraries

# library(readxl)
# library(tidyr)
# library(ggplot2)

Read in Excel Data

# sheet_names <- excel_sheets("C:/Users/alexi/Downloads/Processed EDGAR API Links.xlsx")
# 
# all_sheets <- lapply(sheet_names, function(x) {read_excel("C:/Users/alexi/Downloads/Processed EDGAR API Links.xlsx", sheet = x)})
# 
# names(all_sheets) <- sheet_names
# 
# apple_data <- all_sheets[["Apple"]]
# View(apple_data)

Creating Loop per Company

# for(x in 0:2) {  # Changed to 0:2 since you only have 3 conditions
#   if(x == 0) {    # Parentheses around condition and curly braces
#     fill_color <- "lightblue"
#     variable_name <- "Climate Count"  # This should match your column name
#     category <- "Climate"
#   } else if(x == 1) {  # 'else if' instead of 'elif'
#     fill_color <- "orange"
#     variable_name <- "Sustainable Count"  # Match your column name
#     category <- "Sustainable"
#   } else if(x == 2) {
#     fill_color <- "hotpink"
#     variable_name <- "Environmental Count"  # Match your column name
#     category <- "Environmental"
#   }
#   
#   all_years <- as.character(2002:2024)
# 
#   # Create plot
#   print(  # Need print() inside loops to display ggplot
#     ggplot(apple_data, aes(x = factor(Year), y = .data[[variable_name]])) +  # Dynamic column reference
#       geom_bar(stat = "identity", 
#                fill = fill_color,  # Use the dynamic color
#                width = 0.6,
#                alpha = 0.9,
#                color = "white",
#                linewidth = 0.3) +
#       scale_x_discrete(limits = all_years, drop = FALSE) +
#       scale_y_continuous(limits = c(0, 50), expand = c(0, 0)) +
#       labs(title = paste("Occurrence of", category, "in Apple 10k Forms"),  # Use paste() for string concatenation
#            subtitle = "Annual Frequency (2002-2024)",
#            x = NULL,
#            y = "Count") +
#       theme_minimal(base_size = 13) +
#       theme(
#         plot.title = element_text(hjust = 0.5, face = "bold", size = rel(1.2), margin = margin(b = 8)),
#         plot.subtitle = element_text(hjust = 0.5, color = "gray40", margin = margin(b = 15)),
#         axis.text.x = element_text(angle = 45, hjust = 1, color = "gray30"),
#         axis.text.y = element_text(color = "gray30"),
#         panel.grid.major.x = element_blank(),
#         panel.grid.minor.y = element_blank(),
#         panel.grid.major.y = element_line(color = "gray90", linewidth = 0.3),
#         plot.margin = unit(c(1, 1, 1, 1), "cm")
#       )
#   )
# }

Iterating through 100?

library(readxl)
library(ggplot2)
## Warning: package 'ggplot2' was built under R version 4.3.3
# Get first 5 company names
sheet_names <- excel_sheets("C:/Users/alexi/Downloads/Processed EDGAR API Links.xlsx")[1:100]

for(company in sheet_names) {
  # Read data (no changes to your original graph code)
  company_data <- read_excel("C:/Users/alexi/Downloads/Processed EDGAR API Links.xlsx", 
                           sheet = company)
  
  # Print company header
  cat("\n\n##", company, "\n\n") 
  
  # Your perfect graph code - now with headers for each plot type
  for(x in 0:2) {
    if(x == 0) {
      fill_color <- "lightblue"
      variable_name <- "Climate Count"
    } else if(x == 1) {
      fill_color <- "orange"
      variable_name <- "Sustainable Count"
    } else {
      fill_color <- "hotpink"
      variable_name <- "Environmental Count"
    }
    
    # Your exact plotting code
    print(
      ggplot(company_data, aes(x = factor(Year), y = .data[[variable_name]])) +
        geom_bar(stat = "identity", 
                 fill = fill_color,
                 width = 0.6,
                 alpha = 0.9,
                 color = "white",
                 linewidth = 0.3) +
        scale_x_discrete(limits = as.character(2001:2024), drop = FALSE) +
        scale_y_continuous(expand = c(0, 0)) +
        labs(title = paste(variable_name, "in", company),
             x = NULL,
             y = "Count") +
        theme_minimal() +
        theme(axis.text.x = element_text(angle = 45, hjust = 1))
    )
  }
}
## 
## 
## ## Apple

## 
## 
## ## Cencora

## 
## 
## ## Tesla

## 
## 
## ## Walmart

## 
## 
## ## Amazon

## 
## 
## ## Berkshire Hathaway

## 
## 
## ## CVS Health

## 
## 
## ## Exxon Mobil

## 
## 
## ## Alphabet

## 
## 
## ## McKesson

## 
## 
## ## Costco

## 
## 
## ## Microsoft

## 
## 
## ## Cardinal Health

## 
## 
## ## Chevron

## 
## 
## ## JP Morgan

## 
## 
## ## Toyota Motor

## 
## 
## ## Meta

## 
## 
## ## Morgan Stanley

## 
## 
## ## Citigroup

## 
## 
## ## Verizon Communications

## 
## 
## ## Comcast

## 
## 
## ## Johnson & Johnson

## 
## 
## ## American Express

## 
## 
## ## Procter and Gamble

## 
## 
## ## General Motors

## 
## 
## ## Broadcom

## 
## 
## ## Pepsi
## Warning: Removed 1 row containing missing values or values outside the scale range
## (`geom_bar()`).

## Warning: Removed 1 row containing missing values or values outside the scale range
## (`geom_bar()`).

## Warning: Removed 1 row containing missing values or values outside the scale range
## (`geom_bar()`).

## 
## 
## ## Home Depot

## 
## 
## ## Oracle

## 
## 
## ## Elevance Health

## 
## 
## ## ABBvie

## 
## 
## ## Cisco Systems

## 
## 
## ## Cigna Group

## 
## 
## ## Coca Cola

## 
## 
## ## IBM

## 
## 
## ## Caterpillar

## 
## 
## ## Intel

## 
## 
## ## John Deere

## 
## 
## ## ConocoPhillips

## 
## 
## ## Nvidia

## 
## 
## ## RTX

## 
## 
## ## US Bancorp

## 
## 
## ## Ford Motors

## 
## 
## ## Capital One

## 
## 
## ## Apollo Global Management

## 
## 
## ## Visa

## 
## 
## ## American International Group

## 
## 
## ## Charles Schwab

## 
## 
## ## Progressive

## 
## 
## ## PNC Financial Services

## 
## 
## ## Marathon Petroleum

## 
## 
## ## United Parcel Service

## 
## 
## ## NextEra Energy

## 
## 
## ## Walt Disney

## 
## 
## ## Phillips 66

## 
## 
## ## Metlife

## 
## 
## ## Salesforce

## 
## 
## ## Merck & Co.

## 
## 
## ## Abbott Laboratories

## 
## 
## ## FedEx

## 
## 
## ## Dell Technologies

## 
## 
## ## Lockheed Martin

## 
## 
## ## ELI LILY AND COMPANY

## 
## 
## ## KKR & Co. Inc

## 
## 
## ## Philip Morris International Inc

## 
## 
## ## Valero Energy

## 
## 
## ## Bank of New York Mellon Corp

## 
## 
## ## Prudential Financial

## 
## 
## ## Lowes

## 
## 
## ## Charter Communications

## 
## 
## ## Honeywell International

## 
## 
## ## Duke Energy

## 
## 
## ## Qualcomm

## 
## 
## ## HCA Healthcare

## 
## 
## ## Amgen

## 
## 
## ## Danaher

## 
## 
## ## Mondelez International

## 
## 
## ## Target

## 
## 
## ## Netflix

## 
## 
## ## Travelers

## 
## 
## ## Union Pacific

## 
## 
## ## Mcdonalds

## 
## 
## ## Centene

## 
## 
## ## Paypal

## 
## 
## ## Mastercard

## 
## 
## ## Delta Air Lines

## 
## 
## ## General Dynamics

## 
## 
## ## Nike

## 
## 
## ## Aflac

## 
## 
## ## Occidental Petroleum

## 
## 
## ## Fiserv

## 
## 
## ## SLB

## 
## 
## ## TJX Cos

## 
## 
## ## Automatic Data Processng

## 
## 
## ## Kraft Heinz Company

## 
## 
## ## EOG Resources

## 
## 
## ## Applied Materials

## 
## 
## ## Archer Daniels Midland

## 
## 
## ## Marsh McLennan

## 
## 
## ## Adient